# https://www.bilibili.com/video/BV1W7411G7wP?p=23&spm_id_from=pageDriver&vd_source=8bd7b24b38e3e12c558d839b352b32f4
# 23. 认识数据就是不断地分类和汇总的过程（透视表、分组、聚合）
import pandas as pd
import numpy as np

#pd.options.display.max_columns=999
#pd.options.display.max_rows=999

orders = pd.read_excel('../resources/input_py_tools/023/Orders.xlsx')
#print(orders)
orders['Year'] = pd.DatetimeIndex(orders['Date']).year
#print(orders.head())

# 方法1： 使用Pandas提供的对Excel数据透视表的模拟
pt1 = orders.pivot_table(index='Category', columns='Year', values='Total', aggfunc=np.sum)
print(pt1)

# 方法2： 使用DataFrame的groupby
groups = orders.groupby(['Category', 'Year'])
s = groups['Total'].sum()
c = groups['ID'].count()
pt2 = pd.DataFrame({'Sum':s, 'Count:': c})
print(pt2)